﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Npgsql;
using BibliotecaClasse;
using System.Windows.Forms;

namespace BibliotecaClasse
{
    public class DAOForm
    {

        ConexaoPostgres cpg = ConexaoPostgres.getInstancia();

        /**
         * método para inserir formulario
         */ 
        public void inserirFormulario(Formulario f)
        {
            NpgsqlConnection con = cpg.conectar();

            String sqlInsert = String.Format("INSERT INTO form (nome,ender,num,compl,bairro,cidade,uf,pais,dtnasc,cpf,rg,nmpai,nmmae) VALUES ('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}')", f.Nome, f.Ender, f.Num, f.Compl, f.Bairro, f.Cidade, f.Uf, f.Pais, f.Dtnasc, f.Cpf, f.Rg, f.Nmpai, f.Nmmae);
            try
            {
                con.Open();
                NpgsqlCommand command = new NpgsqlCommand(sqlInsert, con);
                command.ExecuteNonQuery();
            }
            catch (NpgsqlException ex)
            {
                MessageBox.Show(ex.Message);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                con.Close();
            }
        }

        public DataTable getTodosRegistros()
        {
            DataTable dt = new DataTable();
            NpgsqlConnection con = cpg.conectar();

            try
            {
                String sql = String.Format("SELECT * FROM form ORDER BY id");
                NpgsqlDataAdapter adpt = new NpgsqlDataAdapter(sql, con);
                adpt.Fill(dt);
            }
            catch (NpgsqlException ex)
            {
                throw ex;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                cpg.desconectar(con);
            }

            return dt;
        }

        public void deletarFormulario(int id)
        {
            NpgsqlConnection con = cpg.conectar();
            String sql = String.Format("DELETE FROM form WHERE id={0}",id);
            try
            {
                con.Open();
                NpgsqlCommand command = new NpgsqlCommand(sql, con);
                command.ExecuteNonQuery();
            }
            catch (NpgsqlException ex)
            {
                throw ex;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                con.Close();
            }
        }

        public void atualizarFormulario(Formulario f, int cod)
        {
            NpgsqlConnection con = cpg.conectar();
            String sql = String.Format("UPDATE form SET nome='{0}',ender='{1}',num='{2}',compl='{3}',bairro='{4}',cidade='{5}',uf='{6}',pais='{7}',dtnasc='{8}',cpf='{9}',rg='{10}',nmpai='{11}',nmmae='{12}' WHERE id="+cod, f.Nome, f.Ender, f.Num, f.Compl, f.Bairro, f.Cidade, f.Uf, f.Pais, f.Dtnasc, f.Cpf, f.Rg, f.Nmpai, f.Nmmae);

            try
            {
                con.Open();
                NpgsqlCommand command = new NpgsqlCommand(sql, con);
                command.ExecuteNonQuery();
            }
            catch (NpgsqlException ex)
            {
                MessageBox.Show(ex.Message);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                con.Close();
            }
        }

    }
}
